﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MODEL;
using System.Data.OleDb;
using System.Data;
namespace DAL
{
    public class DalRuKu
    {
        public int Insert(RuKu r)
        {
            //string sql = "insert into ruku(classid,dicid,goodname,goodsnumber,price,remark) values(@classid,@dicid,@goodname,@number,@goodsprice,@remark)";
            string sql = "insert into ruku(classid,dicid,goodname,goodsnumber,price,createdate,remark) values(@classid,@dicid,@goodname,@goodsnumber,@price,@createdate,@remark)";
            
            OleDbParameter[] pms = new OleDbParameter[]
                {
                    new OleDbParameter("@classid",r.ClassId),
                    new OleDbParameter("@dicid",r.DicId),
                    new OleDbParameter("@goodname",r.GoodName),
                    new OleDbParameter("@goodsnumber",r.Number),
                    new OleDbParameter("@price",r.Price),
                    new OleDbParameter("@createdate",r.Date.Date.ToString()),
                    new OleDbParameter("@remark",r.Remark), 
                };
            return AccessHelp.ExecteNonQuery(sql,pms);
        }
        public List<RuKu> GetListByPy(string py)
        {
            string sql = "select id,classid,dicid,goodname,goodsnumber,price,createdate,remark,goodspy from ruku where goodspy like @goodspy";
            DataTable dt = AccessHelp.ExecuteReader(sql, new OleDbParameter("@goodspy","%"+py+"%"));
            return TableToList(dt);
        }
         List<RuKu> TableToList(DataTable dt)
        {
            List<RuKu> rukus = new List<RuKu>();
            foreach (DataRow row in dt.Rows)
            {
                RuKu r = new RuKu();
                r.Id = (int)row[0];
                r.ClassId = (int)row[1];
                r.DicId = (int)row[2];
                r.GoodName = row[3].ToString();
                r.Number = (int)row[4];
                r.Price = (int)row[5];
                r.Date = DateTime.Parse(row[6].ToString());
                r.Remark = row[7].ToString();
                r.PY = row[8].ToString();
                rukus.Add(r);
            }
            return rukus;
        }
         public DataTable GetList(string py, DateTime startDate, DateTime endDate)
         {
             string sql = "select c.id,d.goodsname,d.goodsmodel,d.goodsspe,d.goodsunit,c.classid,c.dicid,c.goodsnumber,c.price,c.createdate,c.remark,d.goodspy from ruku c inner join dic d on c.dicid = d.id where d.goodspy like @goodspy and (c.createdate >= @startDate and c.createdate <= @endDate) order by d.classid,c.id ";
             DateTime sDate = startDate;
             DateTime eDate = endDate.AddDays(1).AddMilliseconds(-1000);
             OleDbParameter[] pms = new OleDbParameter[] 
            {
                new OleDbParameter("@goodspy", "%" + py + "%"),
                new OleDbParameter("@startDate",sDate.ToShortDateString()),
                new OleDbParameter("@endDate",eDate.ToShortDateString())
            };
             DataTable dt = AccessHelp.ExecuteReader(sql, pms);
             //return TableToList(dt);
             return dt;
         }


         public List<RuKu> GetAllList()
         {
             string sql = "select id,classid,dicid,goodname,goodsnumber,price,createdate,remark,goodspy from ruku";
             DataTable dt = AccessHelp.ExecuteReader(sql,null);
             return TableToList(dt);
         }

         public int Update(RuKu r)
         {
             string sql = "update ruku set classid=@classid,dicid=@dicid,goodname=@goodname,goodsnumber=@goodsnumber,price=@price,createdate=@createdate,remark=@remark,goodspy=@goodspy where id=@id";
             OleDbParameter[] pms = new OleDbParameter[] 
             {
                 new OleDbParameter("@classid",r.ClassId),
                 new OleDbParameter("@dicid",r.DicId),
                 new OleDbParameter("@goodname",r.GoodName),
                 new OleDbParameter("@goodsnumber",r.Number),
                 new OleDbParameter("@price",r.Price),
                 new OleDbParameter("@createdate",r.Date),
                 new OleDbParameter("@remark",r.Remark),
                 new OleDbParameter("@goodspy",r.PY),
                 new OleDbParameter("@id",r.Id)
             };
             return AccessHelp.ExecteNonQuery(sql, pms);
         }

         public List<RuKu> GetObjectByDicId(int dicid,DateTime date)
         {
             string sql = "select id,classid,dicid,goodname,goodsnumber,price,createdate,remark,goodspy from ruku where dicid=@dicid and (createdate >=@startdate and createdate <=@enddate)";
             string startDate = date.ToString();
             string endDate = date.AddMonths(1).AddSeconds(-1).ToString();
             OleDbParameter[] pms = new OleDbParameter[] {
                 new OleDbParameter("@dicid",dicid),
                 new OleDbParameter("@startdate",startDate),
                 new OleDbParameter("@enddate",endDate)
             };
             DataTable dt = AccessHelp.ExecuteReader(sql,pms);
             return TableToList(dt);
         }
    }
}
